package com.thinkingstar.iads.datacenter.entity.common.dao;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.thinkingstar.iads.common.jsonUtils.AjaxResult;
import org.apache.commons.lang3.StringUtils;
import org.apache.jackrabbit.spi.commons.query.qom.DefaultQOMTreeVisitor;
import org.apache.poi.hssf.record.FeatHdrRecord;
import org.apache.poi.ss.formula.functions.T;
import org.nutz.dao.Chain;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;
import org.nutz.service.EntityService;

import com.thinkingstar.iads.common.serviceImpl.EntityRepositoryServiceImpl;
import com.thinkingstar.iads.common.utils.InitSystemProperties;
import com.thinkingstar.iads.common.utils.StringUtil;
import com.thinkingstar.iads.datacenter.entity.DcEquipment;
import com.thinkingstar.iads.datacenter.entity.DcProjectFieldWidget;

import javassist.compiler.ast.NewExpr;

/**
 * @ClassName: CommonDao 
 * @Description:数据中心_项目_通用dao层
 * @author 1157372219@qq.com 
 * @date 2018年3月19日   
 */
@IocBean(args = {"refer:dao"})
public class CommonDao extends EntityRepositoryServiceImpl<T>{
	
    public CommonDao(Dao dao) {
		super(dao);
	}

	public AjaxResult updateFieldByTableAndField(String record_id,String table_name,String field_name,String value) {
		String update_sql = new String(" update $table_name set $field_name = @value where id = @id");
		Sql sql = Sqls.create(update_sql);
		sql.vars().set("table_name", table_name);
		sql.vars().set("field_name", field_name);
		sql.params().set("value", value);
		sql.params().set("id", record_id);
		dao().execute(sql);
		return AjaxResult.success();
	}

    //清空某个表的某一些符合条件的记录
    public void delRecord(String table_name , String project_id){
    	String del_sql = new String(" delete from $table_name where project_id = @project_id");
    	Sql sql = Sqls.create(del_sql);
    	sql.vars().set("table_name", table_name);
    	sql.params().set("project_id", project_id);
    	dao().execute(sql);
    }
    
    //动态更新数据库
    public int updateBeanById(String table_name , NutMap nutMap){
    	int i = dao().update(table_name, Chain.from(nutMap), Cnd.where("id","=",nutMap.getString("id")));
    	return i;
    }
    
    public void addData(String table_name , List<NutMap> data , String project_id){
    	Iterator<NutMap> iterator = data.iterator();
    	while (iterator.hasNext()) {
			NutMap nutMap = (NutMap) iterator.next();
	    	Chain ch = Chain.make("id",StringUtil.getUUID()).add("project_id", project_id);
	    	Set<Entry<String, Object>> entey_set = nutMap.entrySet();
	    	for(Entry<String, Object> entry : entey_set){
	    		ch = ch.add(entry.getKey(), entry.getValue());
	    	}
	    	dao().insert(table_name, ch);
		}
    }
    
    //动态添加数据(包含序号)
    public void addDataOfNumber(String table_name , List<NutMap> data , String project_id){
    	for(int i=0;i<data.size();i++){
    		NutMap nutMap = data.get(i);
    		Chain ch = Chain.make("id",StringUtil.getUUID()).add("project_id", project_id).add("serial_number", String.valueOf(i+1));
	    	Set<Entry<String, Object>> entey_set = nutMap.entrySet();
	    	for(Entry<String, Object> entry : entey_set){
	    		ch = ch.add(entry.getKey(), entry.getValue());
	    	}
	    	dao().insert(table_name, ch);
    	}
    }
    
    
    //动态添加数据
    public void addBeanByMap(String table_name , NutMap nutMap){
    	Chain ch = Chain.make("id",StringUtil.getUUID());
    	Set<Entry<String, Object>> entey_set = nutMap.entrySet();
    	for(Entry<String, Object> entry : entey_set){
    		if(!"id".equals(entry.getKey())){
    			ch = ch.add(entry.getKey(), entry.getValue());
    		}
    	}
    	dao().insert(table_name, ch);
    }
    
    //动态删除数据
    public int delBeanById(String table_name , String id){
    	int i = dao().clear(table_name, Cnd.where("id", "=" ,id));
    	return i;
    }

    //查询某个表的所有记录数 带筛选条件查询
    public int findDataCountByTableNameAndParam(NutMap param,List<DcProjectFieldWidget> field_list){
        String find_sql = " select count(id) num from $table_name where project_id = @project_id  ";
        for (int i=0;i<field_list.size();i++) {
            String field = field_list.get(i).getField_name();
            if (!field.equals("id") && !field.equals("project_id")){
                if (field_list.get(i).getWidget_type().equals("select")) {
                    if(param.containsKey(field)) {
                        String p_s = (String)param.get(field);
                        if (StringUtils.isNotEmpty(param.get(field).toString())) {
                            find_sql += " and "+field +" ='"+param.get(field).toString()+"' ";
                        }
                    }
                }
            }
        }
        find_sql+=" order by serial_number+0 ";

        Sql sql = Sqls.create(find_sql);
        sql.vars().set("table_name", param.get("table_name"));
        sql.params().set("project_id", param.get("project_id"));
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                int num = 0;
                while (rs.next()) {
                    num = rs.getInt("num");
                }
                return num;
            }
        });
        dao().execute(sql);
        return sql.getInt();
    }

    //查询某个表的所有记录数
    public int findDataCountByTableName(String table_name , String project_id){
    	String find_sql = " select count(id) num from $table_name where project_id = @project_id  ";
    	Sql sql = Sqls.create(find_sql);
    	sql.vars().set("table_name", table_name);
    	sql.params().set("project_id", project_id);
    	sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				int num = 0;
				while (rs.next()) {
					num = rs.getInt("num");
				}
				return num;
			}
		});
    	dao().execute(sql);
    	return sql.getInt();
    }

	//查询某个表的所有有效数据(排序)
	public List <NutMap> findDataListByTableName(String project_id,String table_name){
		List <NutMap> page_list = null;
		Pager pager = null;
		String find_sql = " select * from $table_name where project_id = @project_id ";
		Sql sql = Sqls.create(find_sql);
		sql.vars().set("table_name", table_name);
		sql.params().set("project_id", project_id);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<NutMap> list = new ArrayList<>();
				while (rs.next()){
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		page_list = sql.getList(NutMap.class);
		return page_list;
	}

	//查询某个表的所有有效数据
	public List <NutMap> findOrderDataListByTableName(String project_id,String table_name){
		List <NutMap> page_list = null;
		Pager pager = null;
		String find_sql = " select * from $table_name where project_id = @project_id order by serial_number+0 ";
		Sql sql = Sqls.create(find_sql);
		sql.vars().set("table_name", table_name);
		sql.params().set("project_id", project_id);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<NutMap> list = new ArrayList<>();
				while (rs.next()){
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		page_list = sql.getList(NutMap.class);
		return page_list;
	}

    //查询设计单元的某个表的所有有效数据(按序号排序)
    public QueryResult findDesignCellDataByTableName(NutMap param){
    	List <NutMap> page_list = null;
    	Pager pager = null;
    	String find_sql = " select * from $table_name where project_id = @project_id  order by serial_number+0 ";
    	Sql sql = Sqls.create(find_sql);
    	sql.vars().set("table_name", param.get("table_name"));
    	sql.params().set("project_id", param.get("project_id"));
    	if(param.get("page_num") != null && param.get("page_size") != null){
    		pager = new Pager(param.getInt("page_num"), param.getInt("page_size"));
    		pager.setRecordCount(findDataCountByTableName(param.getString("table_name"),param.getString("project_id")));
    		sql.setPager(pager);
    	}
    	sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
			    List<NutMap> list = new ArrayList<>();
			    while (rs.next()){
			    	NutMap map = new NutMap();
			        ResultSetMetaData metaData = rs.getMetaData();
			        int count = metaData.getColumnCount();
			        for (int i = count; i > 0; i--) {
			            String filed = metaData.getColumnName(i);
			            map.put(filed, rs.getString(filed));
			        }
			        list.add(map);
			    }
			    return list;
			}
		});
    	dao().execute(sql);
    	page_list = sql.getList(NutMap.class);
    	return new QueryResult(page_list,pager);
    }

	//查询设计单元的某个表的所有有效数据(带查询筛选条件)
	public QueryResult findDesignCellDataByTableNameAndParam(NutMap param,List <DcProjectFieldWidget> field_list){
		List <NutMap> page_list = null;
		Pager pager = null;
		String find_sql = " select * from $table_name where project_id = @project_id ";
		for (int i=0;i<field_list.size();i++) {
			String field = field_list.get(i).getField_name();
			if (!field.equals("id") && !field.equals("project_id")){
				if (field_list.get(i).getWidget_type().equals("select")) {
					if(param.containsKey(field)) {
						String p_s = (String)param.get(field);
						if (StringUtils.isNotEmpty(param.get(field).toString())) {
							find_sql += " and "+field +" ='"+param.get(field).toString()+"' ";
						}
					}
				}
			}
		}
		find_sql+=" order by serial_number+0 ";
		Sql sql = Sqls.create(find_sql);
		sql.vars().set("table_name", param.get("table_name"));
		sql.params().set("project_id", param.get("project_id"));
		if(param.get("page_num") != null && param.get("page_size") != null){
			pager = new Pager(param.getInt("page_num"), param.getInt("page_size"));
			pager.setRecordCount(findDataCountByTableNameAndParam(param,field_list));
			sql.setPager(pager);
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<NutMap> list = new ArrayList<>();
				while (rs.next()){
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		page_list = sql.getList(NutMap.class);
		return new QueryResult(page_list,pager);
	}
	
    //查询某个表的所有有效数据
    public QueryResult findDataByTableName(NutMap param){
    	List <NutMap> page_list = null;
    	Pager pager = null;
    	String find_sql = " select * from $table_name where project_id = @project_id ";
    	Sql sql = Sqls.create(find_sql);
    	sql.vars().set("table_name", param.get("table_name"));
    	sql.params().set("project_id", param.get("project_id"));
    	if(param.get("page_num") != null && param.get("page_size") != null){
    		pager = new Pager(param.getInt("page_num"), param.getInt("page_size"));
    		pager.setRecordCount(findDataCountByTableName(param.getString("table_name"),param.getString("project_id")));
    		sql.setPager(pager);
    	}
    	sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
			    List<NutMap> list = new ArrayList<>();
			    while (rs.next()){
			    	NutMap map = new NutMap();
			        ResultSetMetaData metaData = rs.getMetaData();
			        int count = metaData.getColumnCount();
			        for (int i = count; i > 0; i--) {
			            String filed = metaData.getColumnName(i);
			            map.put(filed, rs.getString(filed));
			        }
			        list.add(map);
			    }
			    return list;
			}
		});
    	dao().execute(sql);
    	page_list = sql.getList(NutMap.class);
    	return new QueryResult(page_list,pager);
    }
    
	//根据id查询一个对象
	public <T> T findObjById(Class<T> obj , String id){
		T t = null;
		try {
			t = dao().fetch(obj,id);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return t;
	}
	
	public <T> int delObj(Class<T> obj , String id) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException{
		T t = findObjById(obj,id);
		obj.getMethod("setDel_flag", Boolean.class).invoke(t, true);
		return update(t);
	}

	public List<NutMap> getTables() {
		List<NutMap> list = new ArrayList<NutMap>();
		//mysql 数据库
		String table_schema = InitSystemProperties.get("table_schema");
		Sql sqltable = Sqls.create("select *  from information_schema.TABLES where table_schema='"+table_schema+"' and table_name like 'dc_mention%' or table_name like 'dc_designcell%' or table_name like 'dc_sbgs%' " +
				"or table_name = 'dc_project_mention_information' or table_name = 'dc_project_design_cell' ");
		sqltable.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<NutMap> list = new ArrayList<>();
				while (rs.next()){
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sqltable);
		list = sqltable.getList(NutMap.class);
		return list;
	}

	public List<NutMap> getFieldByTableId(String tableId) {
		List<NutMap> list = new ArrayList<NutMap>();
		//mysql 数据库
		String table_schema = InitSystemProperties.get("table_schema");
		Sql sqltable = Sqls.create("select * from information_schema.`COLUMNS` a " +
				" where a.table_name= '"+tableId+"' and table_schema='"+table_schema+"' ");
		sqltable.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<NutMap> list = new ArrayList<>();
				while (rs.next()){
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sqltable);
		list = sqltable.getList(NutMap.class);
		return list;
	}
}
